//
//  Jikken.m
//  MarketDataAnalyzer 2
//
//  Created by jiro on 10/10/02.
//  Copyright 2010 jiro music. All rights reserved.
//

#import "Jikken.h"
#import "libpq-fe.h"
#import "../Common/DbOperator.h"
#import "InterpolatedData.h"
#import "InterpolatedData2.h"
#import "NotInterpolatedData.h"
#import "../DbTypes/DbUtility.h"
#import "StaticsUtility.h"

using namespace std;

@implementation Jikken

+ (void) go
{
	//	変数定義
	DbOperator* dbOperator = [ DbOperator defaultOperator ];
	NSString* sql;
	PGresult* res;
	
	//	テーブルを生成
//	sql = @"create table analyze_correlation_table2 ( "
//			@"differential integer, "
//			@"code1 text, type1 integer, start_date1 timestamp with time zone, end_date1 timestamp with time zone, "
//			@"code2 text, type2 integer, start_date2 timestamp with time zone, end_date2 timestamp with time zone, "
//			@"coefficient real, slope real, intercept real, interpolation_type integer, "
//			@"primary key ( differential, code1, type1, start_date1, end_date1, code2, type2, start_date2, end_date2, interpolation_type ) ); ";
//	NSLog( @"%@", sql );
//	if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
//	} else {
//		NSLog( @"create table fail" );
//		return;
//	}

	//	期間を設定
	const int period = 365;
	const int months[] = { 1, 4, 7, 10 };	
	NSMutableArray* starts = [ NSMutableArray array ];
	NSMutableArray* ends = [ NSMutableArray array ];
	for ( int i = 0; i < sizeof( months ) / sizeof( int ); i++ ) {
		[ starts addObject: [ NSString stringWithFormat: @"2009-%d-1", months[ i ] ] ];
		[ ends addObject: [ NSString stringWithFormat: @"2010-%d-1", months[ i ] ] ];
	}

	//	銘柄コードを取得
	NSMutableArray* stockCodes = [ NSMutableArray array ];
	sql =
//		@"select code from stock_code_ja_table where exist_flag = true and code not in ( select distinct code2 from analyze_correlation_table );";
	sql = @"select code from stock_code_ja_table where exist_flag = true order by code;";
	if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
		int count = PQntuples( res );
		for ( int i = 0; i < count; i++ ) {
			int code = (int) [ DbUtility parseAsDouble: PQgetvalue( res, i, 0 ) ];
			[ stockCodes addObject: [ NSString stringWithFormat: @"%d", code ] ];
		}
		PQclear( res );
	} else {
		NSLog( @"fail select code sql" );
		PQclear( res );
		return;
	}

	//	為替コードを取得
	NSMutableArray* currencyCodes = [ NSMutableArray array ];
//	sql = @"select code from code_table where = 2;";
	sql = @"select code from stock_code_ja_table where code = '998407';";
	if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
		int count = PQntuples( res );
		for ( int i = 0; i < count; i++ ) {
			[ currencyCodes addObject: [ NSString stringWithFormat: @"%s", PQgetvalue( res, i, 0 ) ] ];
		}
		PQclear( res );
	} else {
		NSLog( @"fail select code sql: %@,\n%@", sql, [ dbOperator errorMessage ] );
		PQclear( res );
		return;
	}

	NSEnumerator* e = [ stockCodes objectEnumerator ];
	id code;
	//	全コードに対する処理
	while ( code = [ e nextObject ] ) {
		NSLog( @"%@", code );
		for ( int i = 0; i < [ starts count ]; i++ ) {
			NSLog( @"start: %@", [ starts objectAtIndex: i ] );
			for ( int k = 0; k < [ starts count ]; k++ ) { 

				//	株価を取得
				vector< double > stockPrices;
				sql = [ NSString stringWithFormat:
						@"select adjusted_end_price from stock_exchange_ja_table " 
						@" where code = '%@' and '%@' < exchange_date and exchange_date <= '%@' order by exchange_date;",
						code, [ starts objectAtIndex: i ], [ ends objectAtIndex: i ] ];
//				NSLog( @"%@", sql );
				if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
					int count = PQntuples( res );
					for ( int j = 0; j < count; j++ ) {
						double price = [ DbUtility parseAsDouble: PQgetvalue( res, j, 0 ) ];
						stockPrices.push_back( price );
					}
///					NSLog( @"code: %@ price %d", code, stockPrices.size() );
					PQclear( res );
				} else {
					NSLog( @"fail to select stock price %@", sql );
					PQclear( res );
					continue;
				}
				if ( stockPrices.size() <= 1 ) { 
					NSLog( @"no data, code: %@", code );
					continue;
				}
				InterpolatedData* stockPriceData = new InterpolatedData( stockPrices, period );
				InterpolatedData* stockPriceData2 = new InterpolatedData2( stockPrices, period );
				InterpolatedData* stockPriceData3 = new NotInterpolatedData( stockPrices, period );

				// 収益率を計算
				vector< double > stockRates( stockPrices.size() - 1 );
				for ( int iii = 0; iii < stockRates.size(); iii++ ) {
					stockRates[ iii ] = stockPrices[ iii ] / stockPrices[ iii + 1 ];
				}
				InterpolatedData* stockRateData = new InterpolatedData( stockRates, period );
				InterpolatedData* stockRateData2 = new InterpolatedData2( stockRates, period );
				InterpolatedData* stockRateData3 = new NotInterpolatedData( stockRates, period );

				//	全為替コードに対する処理
				id currencyCode;
				NSEnumerator* e2 = [ currencyCodes objectEnumerator ];
				while ( currencyCode = [ e2 nextObject ] ) {
				
					//	為替を取得
					vector< double > currencyPrices;
					sql = [ NSString stringWithFormat:
							@"select adjusted_end_price from stock_exchange_ja_table "
							@"where code = '%@' and '%@' < exchange_date and exchange_date < '%@' order by exchange_date;",
							currencyCode, [ starts objectAtIndex: k ], [ ends objectAtIndex: k ] ];
//					NSLog( @"sql: %@", sql );
					if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
						int count = PQntuples( res );
						for ( int j = 0; j < count; j++ ) {
							double price = [ DbUtility parseAsDouble: PQgetvalue( res, j, 0 ) ];
							currencyPrices.push_back( price );
						}
						PQclear( res );
					} else {
						NSLog( @"fail to select currency price %@", sql );
						PQclear( res );
						continue;
					}
					if  ( currencyPrices.size() <= 1 ) {
						continue;
					}
					InterpolatedData* currencyPriceData = new InterpolatedData( currencyPrices, period );
					InterpolatedData* currencyPriceData2 = new InterpolatedData2( currencyPrices, period );
					InterpolatedData* currencyPriceData3 = new NotInterpolatedData( currencyPrices, period );

					// 収益率を計算
					vector< double > currencyRates( stockPrices.size() - 1 );
					for ( int iii = 0; iii < stockRates.size(); iii++ ) {
						currencyRates[ iii ] = currencyPrices[ iii ] / currencyPrices[ iii + 1 ];
					}
					InterpolatedData* currencyRateData = new InterpolatedData( currencyPrices, period );
					InterpolatedData* currencyRateData2 = new InterpolatedData2( currencyPrices, period );
					InterpolatedData* currencyRateData3 = new NotInterpolatedData( currencyPrices, period );

					InterpolatedData* dataArray[] = {
						stockPriceData3, currencyPriceData3, stockRateData3, currencyRateData3,
						stockPriceData, currencyPriceData, stockRateData, currencyRateData,
						stockPriceData2, currencyPriceData2, stockRateData2, currencyRateData2
					};
//					for ( int iii = 0; iii < 12; iii += 2 ) {
					for ( int iii = 0; iii < sizeof( dataArray ) / sizeof( InterpolatedData* ); iii += 2 ) {

						const InterpolatedData* stockData = dataArray[ iii ];
						const InterpolatedData* currencyData = dataArray[ iii + 1 ];

						if ( stockData->GetSampleCount() > 10000 ) {
							NSLog( @"株データが異常 code: %@, 件数: %D", code, stockData->GetSampleCount() );
							for ( int kk = 0; kk < stockPrices.size(); kk++ ) {
//								NSLog( @"%d", stockPrices[ kk ] );
							}
						}

						//	統計量を計算
						double slope = StaticsUtility::CalcCovariance( currencyData, stockData ) / 
											StaticsUtility::CalcVariance( currencyData );
						double intercept = StaticsUtility::CalcMean( stockData ) - 
											slope * StaticsUtility::CalcMean( currencyData );
						double coeffecient = StaticsUtility::CalcCorelationCoefficient( stockData, currencyData );
//						NSLog( @"%f, %f, %f", slope, intercept, coeffecient );

						//	結果をDBに登録
						sql = [ NSString stringWithFormat:
								@"insert into analyze_correlation_table values ( "
								@"%d, "
								@"'%@', 2, '%@', '%@', "
								@"'%@', 0, '%@', '%@', "
								@"%f, %f, %f, %d );",
								( iii / 2 ) % 2,
								currencyCode, [ starts objectAtIndex: k ], [ ends objectAtIndex: k ],
								code, [ starts objectAtIndex: i ], [ ends objectAtIndex: i ],
								coeffecient, slope, intercept, stockData->GetInterpolationType() ];
	//					NSLog( @"%@", sql );
						if ( isnan( coeffecient ) || isnan( slope ) || isnan( coeffecient ) ||
							 isinf( coeffecient ) || isinf( slope ) || isinf( coeffecient ) ) {
							NSLog( @"sql include inf/nan: %@", sql );
						}

						if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
							PQclear( res );
						} else {
							NSLog( @"insert fail %@", [ dbOperator errorMessage ] );
							PQclear( res );
							continue;
						}
					}
					delete currencyPriceData;
					delete currencyPriceData2;
					delete currencyPriceData3;
					delete currencyRateData;
					delete currencyRateData2;
					delete currencyRateData3;
				}
				delete stockPriceData;
				delete stockPriceData2;
				delete stockPriceData3;
				delete stockRateData;
				delete stockRateData2;
				delete stockRateData3;
			}
		}
	}
	
	//	後処理
	[ dbOperator disconnect ];
}

+ (void) go2
{
	//	変数定義
	DbOperator* dbOperator = [ DbOperator defaultOperator ];
	NSString* sql;
	PGresult* res;
	
	//	テーブルを生成
//	sql = @"create table analyze_correlation_table2 ( "
//			@"differential integer, "
//			@"code1 text, type1 integer, start_date1 timestamp with time zone, end_date1 timestamp with time zone, "
//			@"code2 text, type2 integer, start_date2 timestamp with time zone, end_date2 timestamp with time zone, "
//			@"coefficient real, slope real, intercept real, interpolation_type integer, "
//			@"primary key ( differential, code1, type1, start_date1, end_date1, code2, type2, start_date2, end_date2, interpolation_type ) ); ";
//	NSLog( @"%@", sql );
//	if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
//	} else {
//		NSLog( @"create table fail" );
//		return;
//	}

	//	期間を設定
	const int period = 365;
	const int months[] = { 1, 4, 7, 10 };	
	NSMutableArray* starts = [ NSMutableArray array ];
	NSMutableArray* ends = [ NSMutableArray array ];
	for ( int i = 0; i < sizeof( months ) / sizeof( int ); i++ ) {
		[ starts addObject: [ NSString stringWithFormat: @"2009-%d-1", months[ i ] ] ];
		[ ends addObject: [ NSString stringWithFormat: @"2010-%d-1", months[ i ] ] ];
	}

	//	銘柄コードを取得
	NSMutableArray* stockCodes = [ NSMutableArray array ];
	sql =
//		@"select code from stock_code_ja_table where exist_flag = true and code not in ( select distinct code2 from analyze_correlation_table );";
	sql = @"select code from stock_code_ja_table where exist_flag = true order by code;";
	if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
		int count = PQntuples( res );
		for ( int i = 0; i < count; i++ ) {
			int code = (int) [ DbUtility parseAsDouble: PQgetvalue( res, i, 0 ) ];
			[ stockCodes addObject: [ NSString stringWithFormat: @"%d", code ] ];
		}
		PQclear( res );
	} else {
		NSLog( @"fail select code sql" );
		PQclear( res );
		return;
	}

	//	為替コードを取得
	NSMutableArray* currencyCodes = [ NSMutableArray array ];
	sql = @"select code from code_table where = 2;";
	if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
		int count = PQntuples( res );
		for ( int i = 0; i < count; i++ ) {
			[ currencyCodes addObject: [ NSString stringWithFormat: @"%s", PQgetvalue( res, i, 0 ) ] ];
		}
		PQclear( res );
	} else {
		NSLog( @"fail select code sql %@", sql );
		PQclear( res );
		return;
	}

	NSEnumerator* e = [ stockCodes objectEnumerator ];
	id code;
	//	全コードに対する処理
	while ( code = [ e nextObject ] ) {
		NSLog( @"%@", code );
		for ( int i = 0; i < [ starts count ]; i++ ) {
			NSLog( @"start: %@", [ starts objectAtIndex: i ] );
			for ( int k = 0; k < [ starts count ]; k++ ) { 

				//	株価を取得
				vector< double > stockPrices;
				sql = [ NSString stringWithFormat:
						@"select adjusted_end_price from stock_exchange_ja_table " 
						@" where code = '%@' and '%@' < exchange_date and exchange_date <= '%@' order by exchange_date;",
						code, [ starts objectAtIndex: i ], [ ends objectAtIndex: i ] ];
//				NSLog( @"%@", sql );
				if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
					int count = PQntuples( res );
					for ( int j = 0; j < count; j++ ) {
						double price = [ DbUtility parseAsDouble: PQgetvalue( res, j, 0 ) ];
						stockPrices.push_back( price );
					}
///					NSLog( @"code: %@ price %d", code, stockPrices.size() );
					PQclear( res );
				} else {
					NSLog( @"fail to select stock price %@", sql );
					PQclear( res );
					continue;
				}
				if ( stockPrices.size() <= 1 ) { 
					NSLog( @"no data, code: %@", code );
					continue;
				}
				InterpolatedData* stockPriceData = new InterpolatedData( stockPrices, period );
				InterpolatedData* stockPriceData2 = new InterpolatedData2( stockPrices, period );
				InterpolatedData* stockPriceData3 = new NotInterpolatedData( stockPrices, period );

				// 収益率を計算
				vector< double > stockRates( stockPrices.size() - 1 );
				for ( int iii = 0; iii < stockRates.size(); iii++ ) {
					stockRates[ iii ] = stockPrices[ iii ] / stockPrices[ iii + 1 ];
				}
				InterpolatedData* stockRateData = new InterpolatedData( stockRates, period );
				InterpolatedData* stockRateData2 = new InterpolatedData2( stockRates, period );
				InterpolatedData* stockRateData3 = new NotInterpolatedData( stockRates, period );

				//	全為替コードに対する処理
				id currencyCode;
				NSEnumerator* e2 = [ currencyCodes objectEnumerator ];
				while ( currencyCode = [ e2 nextObject ] ) {
				
					//	為替を取得
					vector< double > currencyPrices;
					sql = [ NSString stringWithFormat:
							@"select end_price from currency_exchange_ja_table " 
							@"where code = '%@' and '%@' < exchange_date and exchange_date < '%@' order by exchange_date;",
							currencyCode, [ starts objectAtIndex: k ], [ ends objectAtIndex: k ] ];
//					NSLog( @"sql: %@", sql );
					if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
						int count = PQntuples( res );
						for ( int j = 0; j < count; j++ ) {
							double price = [ DbUtility parseAsDouble: PQgetvalue( res, j, 0 ) ];
							currencyPrices.push_back( price );
						}
						PQclear( res );
					} else {
						NSLog( @"fail to select currency price %@", sql );
						PQclear( res );
						continue;
					}
					if  ( currencyPrices.size() <= 1 ) {
						continue;
					}
					InterpolatedData* currencyPriceData = new InterpolatedData( currencyPrices, period );
					InterpolatedData* currencyPriceData2 = new InterpolatedData2( currencyPrices, period );
					InterpolatedData* currencyPriceData3 = new NotInterpolatedData( currencyPrices, period );

					// 収益率を計算
					vector< double > currencyRates( stockPrices.size() - 1 );
					for ( int iii = 0; iii < stockRates.size(); iii++ ) {
						currencyRates[ iii ] = currencyPrices[ iii ] / currencyPrices[ iii + 1 ];
					}
					InterpolatedData* currencyRateData = new InterpolatedData( currencyPrices, period );
					InterpolatedData* currencyRateData2 = new InterpolatedData2( currencyPrices, period );
					InterpolatedData* currencyRateData3 = new NotInterpolatedData( currencyPrices, period );

					InterpolatedData* dataArray[] = {
						stockPriceData3, currencyPriceData3, stockRateData3, currencyRateData3,
						stockPriceData, currencyPriceData, stockRateData, currencyRateData,
						stockPriceData2, currencyPriceData2, stockRateData2, currencyRateData2
					};
//					for ( int iii = 0; iii < 12; iii += 2 ) {
					for ( int iii = 0; iii < sizeof( dataArray ) / sizeof( InterpolatedData* ); iii += 2 ) {

						const InterpolatedData* stockData = dataArray[ iii ];
						const InterpolatedData* currencyData = dataArray[ iii + 1 ];

						if ( stockData->GetSampleCount() > 10000 ) {
							NSLog( @"株データが異常 code: %@, 件数: %D", code, stockData->GetSampleCount() );
							for ( int kk = 0; kk < stockPrices.size(); kk++ ) {
//								NSLog( @"%d", stockPrices[ kk ] );
							}
						}

						//	関連する基本統計量
//						double stockMean = StaticsUtility::CalcMean( stockData );
//						double stockVariance = StaticsUtility::CalcVariance( stockData );
//						double currencyMean = StaticsUtility::CalcMean( currencyData );
//						double currencyVariance = StaticsUtility::CalcVariance( currencyData );
//						double covariance1 = StaticsUtility::CalcCovariance( currencyData, stockData );
//						double covariance2 = StaticsUtility::CalcCovariance( stockData, currencyData );

						//	統計量を計算
						double slope = StaticsUtility::CalcCovariance( currencyData, stockData ) / 
											StaticsUtility::CalcVariance( currencyData );
						double intercept = StaticsUtility::CalcMean( stockData ) - 
											slope * StaticsUtility::CalcMean( currencyData );
						double coeffecient = StaticsUtility::CalcCorelationCoefficient( stockData, currencyData );
//						NSLog( @"%f, %f, %f", slope, intercept, coeffecient );

						//	結果をDBに登録
						sql = [ NSString stringWithFormat:
								@"insert into analyze_correlation_table values ( "
								@"%d, "
								@"'%@', 2, '%@', '%@', "
								@"'%@', 0, '%@', '%@', "
								@"%f, %f, %f, %d );",
								( iii / 2 ) % 2,
								currencyCode, [ starts objectAtIndex: k ], [ ends objectAtIndex: k ],
								code, [ starts objectAtIndex: i ], [ ends objectAtIndex: i ],
								coeffecient, slope, intercept, stockData->GetInterpolationType() ];
	//					NSLog( @"%@", sql );
						if ( isnan( coeffecient ) || isnan( slope ) || isnan( coeffecient ) ||
							 isinf( coeffecient ) || isinf( slope ) || isinf( coeffecient ) ) {
							NSLog( @"sql include inf/nan: %@", sql );
						}

						if ( DbResponse_Success == [ dbOperator executeQuery: sql intoResult: & res ] ) {
							PQclear( res );
						} else {
							NSLog( @"insert fail %@", [ dbOperator errorMessage ] );
							PQclear( res );
							continue;
						}
					}
					delete currencyPriceData;
					delete currencyPriceData2;
					delete currencyPriceData3;
					delete currencyRateData;
					delete currencyRateData2;
					delete currencyRateData3;
				}
				delete stockPriceData;
				delete stockPriceData2;
				delete stockPriceData3;
				delete stockRateData;
				delete stockRateData2;
				delete stockRateData3;
			}
		}
	}
	
	//	後処理
	[ dbOperator disconnect ];
}

@end
